Workbook Converter tips

The following tips can help you prepare your data and workbooks to ensure a successful conversion process.

Iterative data tips

Iterative data imports allow you to do the following:

  • Update existing contact and address details
  • Add additional addresses
  • Update existing single instance user-defined data
  • Add rows to existing multi-instance user-defined data
  • Add new user-defined data or objects, groups, and memberships
Table 1: Viewing iterative data tips
Data Add Update
Contacts (individuals or organizations) ü ü
Addresses ü ü
Single-instance user-defined data ü ü
Multi-instance user-defined data ü  
User-defined dynamic business objects ü  
Group memberships (existing groups) ü  

Consider the following when performing iterative data updates:

  • Do not include an Address worksheet type on the Contents worksheet if you are not adding or updating addresses.
  • A Contact worksheet type is required, even if only adding or updating addresses or new groups:
    • Only ID and PartyType columns are required.
    • The Contents worksheet must include a row for both the Address and Contact worksheet types when those types are included in the workbook.
  • If adding new single-instance user-defined records, you must include a Contact worksheet type:
    • The worksheet requires a header row. The worksheet does not require any data to be included.
    • If a header is not included, any included data is not added for contacts who do not already have data in the user-defined table.
  • If updating some (not all) columns in a single-instance user-defined table, only the ID column and the updating columns are required on the RelatedData worksheet type.
  • Blank values in user-defined columns for existing contacts are not cleared of existing data. If the existing contact already has data in the user-defined column in the table, a blank value will not overwrite the existing data.
  • You can only add rows to multi-instance user-defined data or activities; you cannot update these items.
General workbook tips

Consider the following when working with the workbook:

  • Excel file must be saved as an .xlsx; older versions of Excel are not supported.
  • Use the correct version of the sample workbook. The workbook version (for example,20-300) must match the iMIS version.
  • Sample workbook files can be found in the following directory:

    • iMIS install directory\iMIS20-WorkbookConverter.zip

  • Hover over column headers in each spreadsheet for tips or help for that column (if available).
  • Do not delete any columns or headers, even if you do not have data for those columns. Doing so can generate errors.
Data preparation tips

Use the VLOOKUP, INDEX and MATCH functions in Excel when merging data from multiple tables or sources. This is useful if you opt to export data from iMIS LAN for a Workbook Converter migration.

The following table identifies additional Excel functions that can be helpful in preparing your data:

Table 2:Viewing data preparation tips
Excel Function Example

VLOOKUP

Finds a supplied value in the first column of a table, and returns the corresponding value from another column.

=VLOOKUP(H3,K1:N9,3,0)

Compare the value of cell H3 with values in the left column of the range K1:N9. When an exact match is found, return a corresponding value found in the third column of K1:N9. If no match is found, return #N/A.

COUNTIF

Returns the number of cells of a range that satisfy a given criteria.

=COUNTIF(E2:E90,"=Colorado")

Count how many cells in the range E2:E90 equal the value Colorado.

FIND

Returns the position of a character from within a text string. This function is case-sensitive.

=FIND(“X”,A3)

When A3 contains the value 57X3A, the function returns 3: this is the character position of X in cell A3.

MID

Returns a specified number of characters from the middle of a text string.

=MID(A7,3,5)

Extracts five characters from cell A7, starting at character position 3.

LEFT

Returns a specified number of characters from the start of a text string.

=LEFT(A3,6)

Extracts the first six characters from the left side of cell A3.

RIGHT

Returns a specified number of characters from the end of a text string.

=RIGHT(A3,3)

Extracts the last three characters from the right side of cell A3.
PROPER

Converts all characters in a text string to proper (Sentence) case.

=PROPER(HELLO world)

All letters that do not immediately follow another letter are set to Upper case. All other characters are Lower case:

Hello World

UPPER

Converts all characters in a text string to Upper case.

=UPPER(Hello World)

Returns text with all letters of all words in Upper case:

HELLO WORLD

LOWER

Converts all characters in a text string to Lower case.

=LOWER(HeLLo WoRLd)

Returns text with all letters of all words in Lower case:

hello world

TRIM

Removes leading and trailing spaces, as well as duplicate spaces.

=TRIM(A3)

When cell A3 contains " Full Time ", the function returns the string "Full Time", where leading and trailing spaces are removed.

LEN

Returns the length of a text string.

=LEN (B2)

When cell B2 contains the phrase Hello World, the function returns a value of 11 (ten characters and one space).

& (concatenation operator)

Joins together text from other cells and text strings to form new text strings.

=C3&”, “&B3

Creates an entry consisting of the text string from cell C3, a comma and space, and then the text from cell B3.

CONCATENATE

Joins together two or more text strings.

 

 =CONCATENATE(A3, " ", C4)

Creates an entry consisting of the text string from cell A3, a space, and then the text from cell C4.

Tips for populating the workbook

Consider the following when populating the workbook:

  • For an initial conversion, you must have at least one Contact worksheet type defined on the Contents worksheet.
  • When creating a new worksheet, copy the column headers from the sample worksheet into the first row of your new worksheet.

    • Note: The column headers must be on the first row, and all column headers must be displayed.

    You are not required to enter data for every column, but you must not delete or rename any column headers.

Tips for populating the workbook with user-defined data

Consider the following when populating the workbook with user-defined data:

  • Do not include RelatedDataDefinition worksheet types for user-defined tables already created with Customizer or Panel Designer.
  • The value in the Data Type column on the Contents worksheet must match the table name.
  • If you need to import data that is not currently supported, for example, Company ID for Company records, import the data using a RelatedData worksheet type and run a post-conversion script to copy the data to the intended location.
Tips for populating column headers for RelatedData sheets

Do the following to quickly and accurately populate column headers for RelatedData sheets:

  • For existing user-defined tables, use sp_columns [TableName] in SQL and copy data from the COLUMN_NAME column.

    • Note: Request assistance from Cloud Services for SQL-related items.

  • For user-defined tables in the workbook:
    1. Select all field names (column A, cell 2 to last field name) in the RelatedDataDefinition sheet.
    2. On the RelatedData sheet, right-click in cell B1, and select Paste Special > Transpose.

Note: It is okay to create RelatedDataDefinition sheets without corresponding RelatedData sheets. You can create user-defined tables or fields to track new data in iMIS even if there is no existing data to import.
This is faster than creating objects manually using Panel Designer.

Searching for potential issues

Consider the following when searching for potential issues with your workbook:

  • Use the Data > Filter feature in Excel to filter and sort on columns.
  • Confirm there is an O (organization) contact record in the workbook for any ID specified as a PrimaryOrganizationId for a P (individual) contact record.
  • Verify all required fields are populated with data:
    • If Blank appears in a filter drop-down for a required column, select the checkbox to view and populate blank fields.
    • Check for blank rows, for example, check the ID column to make sure there are no blank cells that could indicate a blank row.
  • Check for valid values, for example, Address Purpose.
  • Use the LEN function to check for data that might exceed maximum property length.
  • Use the TRIM function to remove any leading or trailing blank spaces from the data.
  • MS Access can be helpful for checking large Workbooks:
    • Import worksheets into an Access database.
    • Sort fields to check for leading spaces or empty rows.
    • Link sheets to detect orphaned records.
    • Export the worksheets after completing reviews and updating as necessary.
Running a test conversion

Note: Request assistance from Cloud Services to run a test conversion.

Do the following to perform a test conversion:

  1. Review Performing pre-conversion tasks.
  2. Perform a test conversion with a smaller subset of data, for example, 1000 contact records with related data.
  3. Ensure the Contents worksheet includes all worksheets you intend to import.
  4. Make sure workbook is closed and saved before running the conversion.
  5. Contact Cloud Services to arrange access to a sandbox migration server.
  6. Launch WorkbookConverter.exe with the Test Only option enabled.
Migrating data in the Cloud

To minimize impact on other clients in the shared server environment, consider the following when running Workbook Converter migrations:

  • ASI Cloud Services provides access to sandbox servers for use by ASI and AiSP/AiC implementation teams.
  • Submit a Hosting Support ticket to schedule a date or time for the migration and request a transfer of database backup to sandbox.
  • After migration, submit a Hosting Support ticket requesting transfer of data back to the production server.
  • Submit a Tech Support ticket for any issues encountered during migration process.
Final data cleanup tips
  • Clear extraneous column data:
    • Select all blank cells in the worksheet, and go to Home > Clear > Clear All.
    • You can also select blank rows and go to Delete > Delete Sheet Rows.
  • Before saving the worksheet, move your cursor to the origin cell (A1) on each worksheet.
  • Restore the database prior to running the final conversion:
    • Do not try to truncate tables instead. You might get a clean test run, but the final conversion will produce Primary Key violations.
  • If creating data in a single-instance user-defined table, deselect Always Create in the table definition before beginning the conversion.
  • More than 50,000 rows: Enable the UseMultitasking option.
  • More than 500,000 rows: Instead of referencing tabs on the workbook, the Contents worksheet should reference separate comma-delimited (.csv) files.
Common error messages and resolutions

The following are some common error messages you might see, and their common resolutions:

Table 3: Viewing common error messages and resolutions
Error Message Resolution
FormatException This error can indicate that a bit field has 0/1 or No/Yes rather than True/False.
True or False is the acceptable format for bit fields.
XXXX Table not defined in workbook This error can indicate that there are trailing spaces in the table name. Clear the blank spaces behind the name in Worksheet Name column in the Contents worksheet.
Error adding Contact with ID XXXXX: Service Error: Cannot insert duplicate key row in object 'dbo.ContactMain' with unique index 'IX_ContactMain_ID'. The duplicate key value is (XXXXX). Check Settings > Contacts > Formula.
Service Error: Violation of PRIMARY KEY constraint 'PK_PrefixRef'. Cannot insert duplicate key in object 'dbo.PrefixRef'. The duplicate key value is (Mrs.).

The migration file has a row with a leading space in front of the prefix Mrs.

Object reference not set to an instance of an object. INNER EXCEPTION: CALL STACK: at WorkbookConverter.WorksheetMapExcelS tream.WorkbookInfo.InitializeWorkbook() …. The customer added formatting such as color coding, bolding, and so forth to the worksheet.
Error: Message: Invalid string: Name must be alphanumeric starting with a letter

This error indicates that there are spaces, even trailing spaces, in the Data Type names on the Contents worksheet, or in the PropertyName names on any of the data definition worksheets.

These Data Types on the Contents worksheet represent new virtual business objects for the user-defined data, and the Property Names in the data definition worksheet become properties of those virtual objects. Neither can contain spaces in the names, or trailing spaces, which might not be apparent.

Warning: OrganizationName for Person record at 'Individuals'![column/row#] will be ignored.

Data has been populated in one of the ignored columns.

Some of the columns in the Contact-type worksheets are not applicable to both individuals and organizations. The column headers always exist in the worksheets and you might want to populate the headers for a party type where the headers are not used, just to assist with data population and reference. If data exists in these columns, these warning messages appear in the log to inform you that the data in those columns is not being imported. These warnings can be ignored, assuming you realize this data is not being imported. If you do not want these warning messages to appear in your log, then you must remove the data from those columns prior to running the migration.

Error: Workbook ID XXXX has not been defined in this workbook and is not an existing contact's alternate ID

And/or:

Error: PrimaryOrganizationId XXXX at Individuals [X,X] refers to an Organization that has not been defined

Data is enclosed in curly brackets {} upon import, causing data to not be found.

In this example, the ID is not found or recognized. This can occur if the cell formatting is set to Text for this data.

The resolution is to set the cell format to General and import the data again.